#delimit;
clear;
set more off;
capture log close;
capture clear matrix;

local in "/data";

local out "/data";

use "`in'/rollup_merged_97_14.dta", clear;
sort pid year;
drop teditseq97 teditseq99 teditseq01 person teditseq03 teditseq04 permid PERMID_201006  PERMID_201206  PERMID_201406;

ren int interest;

keep pid year dtopen dtveri dtclo interest terms_tl hicredit_tl loantype auto_open_tl auto_clo_tl student_open_tl student_clo_tl student_debt_tl mortgage_open_tl mortgage_clo_tl 
install_open_tl install_clo_tl lcred_open_tl lcred_clo_tl unsec_open_tl unsec_clo_tl secgoods_open_tl secgoods_clo_tl lease_open_tl lease_clo_tl ecoa;

dis _N;
count if loantype=="";
drop if loantype=="";
dis _N;

count if dtopen==.;
/*2,280*/
drop if dtopen==.;

count if dtopen==0;
/*1354*/
drop if dtopen==0;
dis _N;
count if dtopen>dtclo & dtopen~=. & dtclo~=. & dtclo~=0;
/*1595*/
drop if dtopen>dtclo & dtopen~=. & dtclo~=. & dtclo~=0;

gen counter=1;
count if loantype=="AUTO";
count if loantype=="AUTO" & interest~=.;
count if loantype=="CCAR";
count if loantype=="CCAR" & interest~=.;
count if loantype=="MORT";
count if loantype=="MORT" & interest~=.;
count if loantype=="STUD";
count if loantype=="STUD" & interest~=.;

/*Sometimes dtclo is coded as . and sometimes as 0. I will replace all to .*/
replace dtclo=. if dtclo==0;

/*Drop all the variables I don't need*/
drop auto_open_tl auto_clo_tl student_debt_tl student_open_tl student_clo_tl mortgage_open_tl mortgage_clo_tl 
	install_open_tl install_clo_tl lcred_open_tl lcred_clo_tl unsec_open_tl unsec_clo_tl secgoods_open_tl secgoods_clo_tl lease_open_tl lease_clo_tl;
drop interest;

sort pid loantype dtopen dtclo year;

gen dtclo_new=dtclo;
/*I will considered closed the accounts that are reported as opened before 2014, and the closing date is based on the last time they were verified. If these accounts are truly opened, we should see them showing up in 2014*/
replace dtclo_new=dtveri if dtclo==. & year<2014;
count if dtclo_new<dtopen; /*108*/
drop if dtclo_new<dtopen;
/*There are some accounts in 2014 that show up as not closed, but the last time they were verified was more than a year ago. I will consider these accounts as closed the day that were verified for the last time*/
count if dtclo==. & year==2014 & dtveri<201306; /*9,503*/
replace dtclo_new=dtveri if dtclo==. & year==2014 & dtveri<201306;

/*For accounts that are opened in 2014, I will replace them with an extreme date (202001) as closing date for convenience, since it is easier to work with a value than a missing*/
replace dtclo_new=202001 if dtclo==. & year==2014;
egen group=group(pid loantype dtopen);
gsort group -dtclo_new;

by group: keep if _n==1; /*2,249,892*/
drop group;
drop dtclo dtveri; /*I don't need this variable anymore*/

gen loantype_alt=loantype;
replace loantype_alt="CCAR_IJ" if (ecoa=="I" | ecoa=="M" | ecoa=="S" | ecoa=="C") & loantype=="CCAR";

/******************************
I WILL DROP ALL CREDIT CARD ACCOUNTS WHICH ARE NOT INDIVIDUAL OR JOINT
******************************/
drop if loantype_alt=="CCAR";
/*28,625*/
drop loantype;
rename loantype_alt loantype;
replace loantype="CCAR" if loantype=="CCAR_IJ";

/*Starting to calculate periods while certain types of accounts were opened*/
egen group=group(pid loantype);
local i=1;
while `i'~=0 {;
capture drop maxsumtoupdate;
sort group dtopen dtclo_new;
by group: egen sumcounter=sum(counter);

gen todrop=1 if group==group[_n-1] & dtopen<=dtclo_new[_n-1];
gen toupdate=1 if group==group[_n+1] & dtclo_new>=dtopen[_n+1] & todrop==.;

gen sumtoupdate=sum(toupdate);
replace sumtoupdate=0 if toupdate==. & todrop==.;

bys sumtoupdate: egen maxdtclo_new=max(dtclo_new);
replace maxdtclo_new=. if sumtoupdate==0;
sort group dtopen dtclo_new;

gen dtclo_new_new=dtclo_new;
replace dtclo_new_new=maxdtclo_new if maxdtclo_new~=.;
/*Now I'll get rid of unnecessary accounts*/
tab todrop;
drop if todrop==1; /*289,239*/ /*22,045*/ /*2,833*/ /*421*/ /*68*/ /*11*/ /*4*/ /*1*/

egen maxsumtoupdate=max(sumtoupdate);
local i=maxsumtoupdate;

drop todrop toupdate sumtoupdate maxdtclo_new dtclo_new sumcounter;
rename dtclo_new_new dtclo_new;
};
rename dtclo_new dtclo;
drop maxsumtoupdate;
drop year;
drop group;

sort pid;
merge m:1 pid using "`out'/age1997.dta";
drop _merge;

drop if dtopen==.;

gen dtopen_temp=string(dtopen)+string(28);
gen dtopen_st=date(dtopen_temp,"YMD");
format dtopen_st %td;
gen month=month(dtopen_st);
drop dtopen_st;

gen dtopen_temp2=string(dtopen)+string(30) if month==4 | month==6 | month==9 | month==11;

replace dtopen_temp2=string(dtopen)+string(31) if month==1 | month==3 | month==5 | month==7 | month==8 | month==10 | month==12;
replace dtopen_temp2=string(dtopen)+string(28) if month==2;
gen dtopen_st=date(dtopen_temp2,"YMD");
format dtopen_st %td;


drop dtopen_temp dtopen_temp2 month;

/*Dropping accounts that were opened before the person was 16yo*/
gen todrop=1 if age-(day_of_TU_1997-dtopen_st)/365<15;
tab loantype todrop;

drop if todrop==1;
drop dtopen_st day_of_TU_1997 age;
drop todrop;

replace loantype="student" if loantype=="STUD";
replace loantype="ccard" if loantype=="CCAR";
replace loantype="mortgage" if loantype=="MORT";
replace loantype="auto" if loantype=="AUTO";
replace loantype="install" if loantype=="INST";
replace loantype="lcred" if loantype=="LCRE";
replace loantype="unsec" if loantype=="UNSE";
replace loantype="secgoods" if loantype=="SECG";
replace loantype="lease" if loantype=="LEAS";

bys pid loantype: gen open=sum(counter);

local var="student ccard auto mortgage install lcred unsec secgoods lease";
*local var="student";
foreach j of local var {;
preserve;
keep if loantype=="`j'";
sum open;
local m=r(max);
forvalues i=1(1)`m' {;
gen `j'_dtopen`i'_t=dtopen if open==`i';
gen `j'_dtclo`i'_t=dtclo if open==`i';
bys pid: egen `j'_start`i'=max(`j'_dtopen`i'_t);
bys pid: egen `j'_end`i'=max(`j'_dtclo`i'_t);
};
sort pid;
keep pid `j'_start* `j'_end*;
bys pid: keep if _n==1;
save "`out'/temp_`j'_open_clo.dta", replace;
restore;
};

use  "`out'/temp_student_open_clo.dta", clear;
merge 1:1 pid using  "`out'/temp_ccard_open_clo.dta";
drop _merge;
merge 1:1 pid using  "`out'/temp_auto_open_clo.dta";
drop _merge;
merge 1:1 pid using  "`out'/temp_mortgage_open_clo.dta";
drop _merge;
merge 1:1 pid using  "`out'/temp_install_open_clo.dta";
drop _merge;
merge 1:1 pid using  "`out'/temp_lcred_open_clo.dta";
drop _merge;
merge 1:1 pid using  "`out'/temp_unsec_open_clo.dta";
drop _merge;
merge 1:1 pid using  "`out'/temp_secgoods_open_clo.dta";
drop _merge;
merge 1:1 pid using  "`out'/temp_lease_open_clo.dta";
drop _merge;

merge 1:m pid using "`out'/tutemp_tomerge.dta";
drop _merge;
compress;
order pid year;
sort pid year;

sort pid year;
foreach v of varlist student_start* student_end* mortgage_start* mortgage_end* auto_start* auto_end* ccard_start* ccard_end* install_start* install_end* lcred_start* lcred_end* unsec_start* unsec_end* secgoods_start* secgoods_end* lease_start* lease_end* {;
qui gen `v'_t=string(`v');
qui gen `v'_year=substr(`v'_t,1,4);
qui gen `v'_month=substr(`v'_t,5,2);
drop `v'_t;

qui destring `v'_year `v'_month, replace;
qui gen `v'_mdy=mdy(`v'_month,28,`v'_year) if `v'_month==2;
qui replace `v'_mdy=mdy(`v'_month,30,`v'_year) if `v'_month==4 | `v'_month==6 | `v'_month==9 | `v'_month==11;
qui replace `v'_mdy=mdy(`v'_month,31,`v'_year) if `v'_month==1 | `v'_month==3 | `v'_month==5 | `v'_month==7 | `v'_month==8 | `v'_month==10 | `v'_month==12;
drop `v'_year `v'_month;
drop `v';
by pid: egen max_`v'=max(`v'_mdy);
rename `v'_mdy `v';
format `v' %td;
format max_`v' %td;
};

save "`out'/tu_panel.dta", replace;

erase "`out'/temp_ccard_open_clo.dta";
erase "`out'/temp_student_open_clo.dta";
erase "`out'/temp_auto_open_clo.dta";
erase "`out'/temp_mortgage_open_clo.dta";
erase "`out'/temp_install_open_clo.dta";
erase "`out'/temp_lcred_open_clo.dta";
erase "`out'/temp_unsec_open_clo.dta";
erase "`out'/temp_secgoods_open_clo.dta";
erase "`out'/temp_lease_open_clo.dta";
